package com.haiheng.module_main.utils;

import android.content.Context;
import android.net.Uri;
import android.os.Environment;
import android.util.Log;


import com.haiheng.common.constant.AppConstant;
import com.haiheng.common.utils.BookUtil;
import com.haiheng.library.log.LogUtils;
import com.haiheng.library_base.db.BookInventoryInfo;
import com.haiheng.library_base.utils.DateUtils;
import com.haiheng.library_base.db.BookInfoBean;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * @Des: excel读取工具类
 * @Author: hwz
 * @Date:2021/12/3 0003
 * @Version: 1.0
 *
 * 参考
 * https://blog.csdn.net/u014743890/article/details/91045033?spm=1001.2101.3001.6650.1&utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7ECTRLIST%7Edefault-1.fixedcolumn&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7ECTRLIST%7Edefault-1.fixedcolumn
 */
public class ExcelUtils {


    /**
     * 读取excel   （xls和xlsx）
     * @return
     */
    public static List<Map<String, String>> readExcel(Context context,Uri uri, String logFilePath, String columns[]) {
//        String logFilePath = Environment.getExternalStorageDirectory() + File.separator + "excel";
//        String logFilePath = Environment.getStorageDirectory() + File.separator + "excel";
//        File file = new File(logFilePath, "test.xlsx");
//        File file = new File(logFilePath);
//        String filePath=file.getAbsolutePath();
        Sheet sheet = null;
        Row row = null;
        Row rowHeader = null;
        List<Map<String, String>> list = null;
        String cellData = null;
        Workbook wb = null;
//        if (filePath == null) {
//            return null;
//        }
        try {
            InputStream fis = context.getContentResolver().openInputStream(uri);


            wb = WorkbookFactory.create(fis);
//            if (POIFSFileSystem.hasPOIFSHeader(fis)) {
//                wb = new HSSFWorkbook(fis);
//            }
//            if (POIXMLDocument.hasOOXMLHeader(fis)) {
//                wb = new XSSFWorkbook(OPCPackage.open(fis));
//            }

//            String extString = filePath.substring(filePath.lastIndexOf("."));
//        InputStream is = null;
//            is = new FileInputStream(filePath);
//            if (".xls".equals(extString)) {
//                wb = new HSSFWorkbook(is);
//            } else if (".xlsx".equals(extString)) {
//                wb = new XSSFWorkbook(is);
//            } else {
//                ToastUtils.showShort("文件格式不正确");
//                wb = null;
//            }
            if (wb != null) {
                // 用来存放表中数据
                list = new ArrayList<Map<String, String>>();
                // 获取第一个sheet
                sheet = wb.getSheetAt(0);
                // 获取最大行数
                int rownum = sheet.getPhysicalNumberOfRows();
                // 获取第一行
                rowHeader = sheet.getRow(0);
                row = sheet.getRow(0);
                // 获取最大列数
                int colnum = row.getPhysicalNumberOfCells();
                for (int i = 1; i < rownum; i++) {
                    Map<String, String> map = new LinkedHashMap<String, String>();
                    row = sheet.getRow(i);
                    if (row != null) {
                        for (int j = 0; j < colnum; j++) {
                            if(columns[j].equals(getCellFormatValue(rowHeader.getCell(j)))){
                                cellData = (String) getCellFormatValue(row
                                        .getCell(j));
                                map.put(columns[j], cellData);
                                /*DecimalFormat df = new DecimalFormat("#");
                                System.out.println(    df.format(cellData));*/
                                Log.e("yy","cellData="+cellData);
                                Log.e("yy","map="+map);
                            }
                        }
                    } else {
                        break;
                    }
                    list.add(map);
                }
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
            LogUtils.e(e.getMessage());
        } catch (IOException e) {
            e.printStackTrace();
            LogUtils.e(e.getMessage());
        }catch (Exception e){
            e.printStackTrace();
            LogUtils.e(e.getMessage());
        }
        return list;
    }

    /**	获取单个单元格数据
     * @param cell
     * @return
     * @author lizixiang ,2018-05-08
     */
    public static Object getCellFormatValue(Cell cell) {
        Object cellValue = null;
        if (cell != null) {
            // 判断cell类型
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC: {
                    cellValue = String.valueOf(cell.getNumericCellValue());
                    break;
                }
                case Cell.CELL_TYPE_FORMULA: {
                    // 判断cell是否为日期格式
                    if (DateUtil.isCellDateFormatted(cell)) {
                        // 转换为日期格式YYYY-mm-dd
                        cellValue = cell.getDateCellValue();
                    } else {
                        // 数字
                        cellValue = String.valueOf(cell.getNumericCellValue());
                    }
                    break;
                }
                case Cell.CELL_TYPE_STRING: {
                    cellValue = cell.getRichStringCellValue().getString();
                    break;
                }
                default:
                    cellValue = "";
            }
        } else {
            cellValue = "";
        }
        return cellValue;
    }

    public static void onCreateSheet(HSSFWorkbook workBook,
                                     String sheetName,
                                     int tabale,List<BookInventoryInfo> bookInfoBeanList){
        //新建sheet
        HSSFSheet sheet = workBook.createSheet(sheetName);

        //创建单元格样式
        HSSFCellStyle style = getStyle(workBook);
        //创建第一行
        Row headerRow =sheet.createRow(0);
        // 设置第一行：48pt的字体的内容
//            headerRow.setHeightInPoints(30);
        //创建第一行中第一单元格
        String[] title;
        if (tabale == AppConstant.Companion.getTABALE_ONE()){
            title = new String[]{"条码号", "名称", "状态", "位置","盘点时间"};
            for (int i = 0; i < title.length; i++) {
                Cell headerCell=headerRow.createCell(i);
                headerCell.setCellValue(title[i]);
                headerCell.setCellStyle(style);
            }
            for (int i = 0; i < bookInfoBeanList.size(); i++) {
                //创建行
                HSSFRow row = sheet.createRow(i + 1);
                BookInventoryInfo info = bookInfoBeanList.get(i);
                for (int j = 0; j < title.length; j++) {
                    //创建列单元格
                    HSSFCell cell = row.createCell(j);
                    cell.setCellStyle(style);
                    switch (j) {
                        case 0://条码
                            cell.setCellValue(info.getBarCode());
                            break;
                        case 1:
                            cell.setCellValue(info.getBookTitle());
                            break;
                        case 2:
                            cell.setCellValue(BookUtil.INSTANCE.getBookShelfStatus(info.getBookOnShelfStatus()));
                            break;
                        case 3:
                            cell.setCellValue(info.getCurrentContainerName());
                            break;
                        case 4:
                            cell.setCellValue(DateUtils.formatDateTime(info.getCreateDate(),DateUtils.DATE_FORMAT_2));
                            break;
                    }
                }
            }
        }
   /*

        for (int i = 0; i < orderNumbers.size(); i++) {
            //创建行
            HSSFRow row = sheet.createRow(i+1);
            OrderNumber info = orderNumbers.get(i);
            for (int j = 0; j < 4; j++) {
                //创建列单元格
                HSSFCell cell = row.createCell(j);
                cell.setCellStyle(style);
                switch (j) {
                    case 0://时间
                        cell.setCellValue(info.getOrderNum());
                        break;
                    case 1:
                        cell.setCellValue(info.getName());
                        break;
                    case 2:
                        cell.setCellValue(info.getGoodName());
                        break;
                    case 3:
                        cell.setCellValue(DateFormatUtils.formatDate(info.getCreateTime()));
                        break;
                }

                //合并单元格，参数是起始行，结束行，起始列，结束列
//                    sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex + 1, i, i));
            }
        }*/
    }

    //数据导出到excel
    public static void writeExcel(String exFileName,  List<BookInventoryInfo> bookInfoBeanList) throws Exception {
        String ROOT_PATH = Environment.getExternalStorageDirectory().getAbsolutePath() + "/Ssitexcel/";
//        String ROOT_PATH = Environment.getExternalStoragePublicDirectory(Environment.DIRECTORY_DOCUMENTS) + "/Ssitexcel/";
        try {
            File dir = new File(ROOT_PATH);
            if (!dir.exists()) {
                dir.mkdirs();
            }
            String exPath = ROOT_PATH + exFileName + ".xls";
            LogUtils.e("导出路径："+exPath);
            File file = new File(exPath);
            if (!file.exists()) {
                file.createNewFile();
            }
            OutputStream out = new FileOutputStream(file);

            //新建excel
            HSSFWorkbook workBook = new HSSFWorkbook();
            List<BookInventoryInfo> tabaleOneList = new ArrayList<>();
            List<BookInfoBean> tabaleTwoList = new ArrayList<>();
            List<BookInfoBean> tabaleThirdList = new ArrayList<>();
            for (BookInventoryInfo o : bookInfoBeanList) {
                tabaleOneList.add(o);
            }
            onCreateSheet(workBook,"盘点总数",AppConstant.Companion.getTABALE_ONE(),tabaleOneList);
            workBook.write(out);
            out.flush();
            out.close();
            LogUtils.i("Excel文件保存到 ：" + ROOT_PATH);
        } catch (Exception e) {
            e.printStackTrace();
            LogUtils.e("Excel文件" + exFileName + "生成失败：" + e);
            throw e;
        }
    }


    public static HSSFCellStyle getStyle(HSSFWorkbook workbook) {

        //设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        //设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        //设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        //设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        //设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        //设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        //设置右边框颜色;
        style.setRightBorderColor(HSSFColor.BLACK.index);
        //设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        //设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.BLACK.index);
        //设置自动换行;
        style.setWrapText(false);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        // 设置单元格字体
        HSSFFont font = workbook.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font.setFontName("宋体");
        font.setFontHeight((short) 200);
        style.setFont(font);

        return style;
    }
}
